package utility;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class renumber extends HttpServlet {
	private static final long serialVersionUID = 1L;
	private String oracleUser = "azienda";
    private String oraclePwd  = "azienda";
    private Connection conn;
    private String driver,oracle,username;
	
	
    public renumber() {
        
    	super();
    }
    
    public void init(ServletConfig sc) throws ServletException
    {
    	super.init(sc);
		driver = "oracle.jdbc.driver.OracleDriver";
		oracle = "jdbc:oracle:thin:@localhost:1521:xe";
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
    }
    
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String qry="";
		int last = 0;
		int codice=0;
		int seriale=0;
		int ok = 0;
		String cat="";
		
		username  = "" + request.getParameter("username");
		if (username==null) response.sendRedirect("hom/loginerr.jsp");
		
		try {
			conn = DriverManager.getConnection(oracle, oracleUser, oraclePwd);
			conn.setAutoCommit(false);
			
			Statement st = conn.createStatement();
			Statement stRen = conn.createStatement();
			qry = "select b.numeroinventariogenerico as seriale,s.categoriabene as cat from "
				+ "bene b inner join sottocategoriabene s on b.sottocategoriabene=s.codice " 
				+ "order by b.numeroinventariogenerico";
			
			System.out.println(qry);
			ResultSet rs = st.executeQuery(qry);
			ResultSet ls=null;
			
			while(rs.next())
			{
				System.out.println("record " + rs.getRow());
				seriale=rs.getInt("seriale");
				cat=rs.getString("cat");
				System.out.println("Seriale:"+seriale+", cat:"+cat);
				//PRELEVO L'ULTIMO NUMERO INSERITO RISPETTO ALLA CATEGORIA
				try {
					qry = "select numero from seriali where categoria = '" + cat + "'";
					ls=stRen.executeQuery(qry);
					ls.next();
					if (ls.getString("numero")==null)
						last=1;
					else
						last=ls.getInt("numero")+1;
				} catch (SQLException e) {
					last=1;
				}
				ls.close();
				//AGGIORNO IL BENE CON IL NUMERO SERIALE CORRETTO
				qry = "update bene set numeroinventarioseriale='"+last+"/"+cat+"' where numeroinventariogenerico="+seriale;
				System.out.println(qry);
				stRen.executeUpdate(qry);
				//PRELEVO L'ULTIMO CODICE DALLA TABELLA SERIALI
				try {
					qry = "select max(codice) as lastcod from seriali";
					ls=stRen.executeQuery(qry);
					ls.next();
					if (ls.getString("lastcod")==null)
						codice=1;
					else
						codice=ls.getInt("lastcod")+1;
				} catch (Exception e) {
					codice=1;
				}
				
				//MODIFICO L'INDICE DEI NUMERI SERIALI
				qry = "update seriali set numero="+last+" where categoria='"+cat+"'";
				System.out.println(qry);
				ok=stRen.executeUpdate(qry);
				if(ok==0)
				{   //SE NON ESISTE IL RECORD LO CREO...
					System.out.println("INSERT NEW SERIALE");
					qry = "insert into seriali values("+codice+",'"+cat+"',"+last+")";
					System.out.println(qry);
					ok=stRen.executeUpdate(qry);
				}
				System.out.println("Next record...");
			}
			
			rs.close();
			st.close();
			stRen.close();
			conn.commit();
			conn.close();
			
			response.sendRedirect("adm/ben/elenco_beni_inventariati.jsp?order=1");
			
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		
	}

}
